import concurrent
import datetime
import json
from concurrent.futures import ThreadPoolExecutor, as_completed
from common.setting import ensure_path_sep
from testcases.monitor.aliexpress.test_tool.get_template_id import GetTemplateId
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink


class AutoFilter:
    zhcxkj_center_strategy_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                       "HWY-ZH-GZ-RDS-strategy")

    zhcxkj_center_listing_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                      "zhcxkj_center_listing_comprehensive")

    ''' 实现逻辑：
        1.查询 sku 关联listing,listing 下关联变体sku
        2.查询 所有变体sku 库存
        3.获取满足条件的所有 listing
        4.根据 策略设置的可用库存条件 过滤listing
    '''
    def __init__(self,strategy_id,):
        self.strategy_id = strategy_id
        print(f'执行策略{self.strategy_id}：')

    # 获取 满足商品状态 的所有listing
    def get_valid_listing(self,source_id, batch_size=1000):
        get_strategy_type_sql = """
                                SELECT strategy_type FROM `zhcxkj_center_strategy_comprehensive`.`t_strategy_aliexpress` 
                                WHERE `id` = %s 
                                """
        strategy_type = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(get_strategy_type_sql,
                                                                                     (self.strategy_id,))[0][0]
        status = 0
        if int(strategy_type) == 4:
            status += 1
        else:
            status += 2
        get_offline_value_sql = f"""
                      SELECT pf_item_id
                      FROM zhcxkj_center_listing_comprehensive.t_aliexpress_listing  WHERE source_id = %s
                      and status = %s and is_deleted = 0
                     """
        offline_list = self.zhcxkj_center_listing_comprehensive_db.fetch_all_tuple(get_offline_value_sql,
                                                                                   (source_id, status))
        get_template_id_sql = """
                            SELECT filter_val FROM `zhcxkj_center_strategy_comprehensive`.`t_strategy_rule_aliexpress` 
                            WHERE `strategy_id` = %s AND `rule_code` = 'listing_template_filter' 
                            """
        template_data = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(get_template_id_sql,
                                                                                   (self.strategy_id,))
        template_id = template_data[0][0]
        coll_name = GetTemplateId(template_id).get_coll_name()
        template_list_mongo = MongoDBLink(
            uri='mongodb://reader:Vly07YmbY0kLt80Y?@121.37.240.172:8635,121.37.230.118:8635/test?authSource=admin&replicaSet=replica',
            dbname='zhcxkj_center_strategy', coll_name=coll_name)

        pf_item_id_list = list(map(lambda x: str(x[0]), offline_list))
        get_reason = """
                            SELECT filter_val FROM `zhcxkj_center_strategy_comprehensive`.`t_listing_template_rule_aliexpress` 
                            WHERE `template_id` = %s  and  rule_code = "off_reason"
                            """
        template_reason_mongo = MongoDBLink(
            uri='mongodb://reader:MubmnYb1caAmZjOJ#@116.205.136.83:8635,139.9.210.226:8635/?authSource=admin',
            dbname='zhcxkj_java_center', coll_name="t_listing_pull_list_original_aliexpress")
        off_reason_data = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(get_reason,
                                                                                       (self.strategy_id,))
        # 判断 删除或 下架
        if status == 1 and off_reason_data:
            off_reason = off_reason_data[0][0].split(',')
            for i in range(0, len(pf_item_id_list), batch_size):
                batch = pf_item_id_list[i:i + batch_size]

                filter_object = {
                    "$and": [
                        {"sourceId": source_id},
                        {"wsDisplay": {"$in": off_reason}},
                        {"productId": {"$in": batch}},
                    ]}
                projection1 = {"_id": 0, "pfListingId": 1}
                res = template_reason_mongo.select(filter_object, projection1)
                listing_id_set = set(map(lambda x: x.get('pfListingId'), res))
                return listing_id_set
        else:
            for i in range(0, len(pf_item_id_list), batch_size):
                batch = pf_item_id_list[i:i + batch_size]
                filter_list_object = {
                    "$and": [
                        {"sourceId": source_id},
                        {"pfListingId": {"$in": batch}},
                    ]
                }
                projection1 = {"_id": 1, "pfListingId": 1}
                res = template_list_mongo.select(filter_list_object, projection1)
                listing_rst_set = set(map(lambda x: x.get('pfListingId'), res))
                return listing_rst_set

    # 获取 策略规则
    def get_rule_by_strategy_id(self):
        strategy_rule_dict = {}
        get_strategy_rule_sql = f"""
                                SELECT filter_min_val,filter_max_val
                                FROM zhcxkj_center_strategy_comprehensive.t_strategy_rule_aliexpress
                                WHERE strategy_id = %s and is_deleted = 0 and rule_code = "available_stock"
                                """
        strategy_rule = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(get_strategy_rule_sql,
                                                                                     (self.strategy_id,))
        if strategy_rule[0] != ('', ''):
            for data in strategy_rule:
                strategy_rule_dict['filter_min_val'] = int(data[0][0])
                strategy_rule_dict['filter_max_val'] = int(data[0][1])
                # {'filter_min_val': 1, 'filter_max_val': 10}
                return strategy_rule_dict
        else:
            return False

    def verification_results(self,source_id):
        get_mysql_results_sql = f"""
                        SELECT product_id
                        FROM zhcxkj_center_strategy_comprehensive.t_strategy_execute_queue_off_delete_aliexpress
                        WHERE  strategy_id = %s and source_id = %s and `date_int` = %s 
                        """
        data_int = datetime.datetime.now().strftime('%Y-%m-%d').replace("'","").replace("-","")
        actual_value = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(
            get_mysql_results_sql,(self.strategy_id,source_id,int(data_int)))

        actual_set = set(map(lambda x: str(x[0]), actual_value))
        filter_set = set(self.get_valid_listing(source_id))
        different_sql_set = filter_set - actual_set
        different_mongo_set = actual_set - filter_set

        if different_sql_set is None and different_mongo_set is None:
            print("无差别")
            return True
        else:
            print(f'{source_id}:进入数据库的listing有{len(actual_set)}个,{source_id}:程序过滤出的listing有{len(filter_set)}个,'
                  f'{source_id}:mongo多余的数据有:{different_sql_set},{source_id}:mysql多余的数据有:{different_mongo_set}')
            print(f'-----------------------------------------')

    def verification_queue(self):
        get_source_list = '''
        SELECT import_val FROM `zhcxkj_center_strategy_comprehensive`.`t_strategy_import_filter_aliexpress` 
        WHERE  `import_id` = %s
        '''
        source_id_data = self.zhcxkj_center_strategy_comprehensive_db.fetch_all_tuple(get_source_list, (self.strategy_id,))
        source_id_list = set(map(lambda x: int(x[0]), source_id_data))
        with ThreadPoolExecutor(max_workers=10) as executor:
            future_to_source_id = {executor.submit(self.verification_results, source_id): source_id for source_id in
                                   source_id_list}
            for future in concurrent.futures.as_completed(future_to_source_id):
                try:
                    future.result()
                except Exception as exc:
                    print(f'Error fetching data for page {future_to_source_id[future]}: {exc}')


if __name__ == '__main__':
    # print(AutoOffline(53).available_inventory(1005006087307212))
    AutoFilter(10).verification_queue()
    # AutoFilter(10).verification_results(10956)
    # AutoFilter(10).verification_queue()
    # print(AutoFilter(4).get_valid_listing(11078))
    # AutoOffline(55).verification_results(5204)

    # print(AutoDelete(37).available_inventory(1005005704439773))

